﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Data;
using Light.Utility;
using System.Collections;

namespace Light.Framework
{
    /// <summary>
    /// 查询参数
    /// </summary>
    [Serializable]
    public partial class SearchArgs
    {

        public SearchArgs()
        {
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="keyword">输入的关键字</param>
        /// <param name="searchFields">搜索属性字段列表</param>
        public SearchArgs(string keyword, List<string> searchFields)
            : this()
        {
            if (!String.IsNullOrEmpty(keyword))
            {
                foreach (var field in searchFields)
                {
                    this[field] = keyword;
                }
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="keyword">输入的关键字</param>
        /// <param name="searchFields">搜索属性字段字典</param>
        public SearchArgs(string keyword, Dictionary<string, string> searchFields)
            : this()
        {
            if (!String.IsNullOrEmpty(keyword))
            {
                foreach (var key in searchFields.Keys)
                {
                    this[key] = keyword;
                }
            }
        }

        #region private

        private SearchLogic logic = SearchLogic.Or;
        private List<string> listFields = new List<string>(); //用来存储所有查询的属性项或字段项
        private Dictionary<string, object> fields = new Dictionary<string, object>();
        private Dictionary<string, SearchExpression> expressions = new Dictionary<string, SearchExpression>();

        #endregion

        /// <summary>
        /// 是否包含Or查询条件
        /// </summary>
        protected bool HasOr
        {
            get { return fields.Count > 0; }
        }

        /// <summary>
        /// 是否包含And查询条件
        /// </summary>
        protected bool HasAnd
        {
            get { return expressions.Count > 0; }
        }

        /// <summary>
        /// 获取所有查询了的字段或属性
        /// </summary>
        public List<string> Fields
        {
            get
            {
                return listFields;
            }
        }

        /// <summary>
        /// 设置搜索项，共用属性Logic
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public object this[string name]
        {
            get
            {
                if (fields.Keys.Contains(name))
                    return fields[name];
                else
                    return string.Empty;
            }
            set
            {
                if (fields.Keys.Contains(name))
                    fields[name] = value;
                else
                {
                    listFields.Add(name);
                    fields.Add(name, value);
                }
            }
        }

        /// <summary>
        /// 日期：开始
        /// </summary>
        public DateTime DateStart { get; set; }

        /// <summary>
        /// 日期：结束
        /// </summary>
        public DateTime DateEnd { get; set; }

        /// <summary>
        /// 日期：字段名/属性名
        /// </summary>
        public string DateField { get; set; }

        /// <summary>
        ///查询条件逻辑运算符(AND,OR) 默认为OR
        /// </summary>
        public SearchLogic Logic
        {
            get { return logic; }
            set { logic = value; }
        }

        /// <summary>
        /// 添加搜索项
        /// </summary>
        /// <param name="logic"></param>
        /// <param name="field"></param>
        /// <param name="value">查询值</param>
        public void Add(SearchLogic logic, string field, object value)
        {
            this.Add(logic, field, SearchOperator.Eq, value);
        }

        /// <summary>
        /// 添加搜索项
        /// </summary>
        /// <param name="logic">Or|And</param>
        /// <param name="field">查询项</param>
        /// <param name="optor">运算符</param>
        /// <param name="values">查询值，可匹配多个</param>
        public void Add(SearchLogic logic, string field, SearchOperator optor, params object[] values)
        {

            listFields.Add(field);

            //如果values未传值，说明是调用的 Add(logic,field,value) value为int类型，系统自动将int转optor了，在此纠正回来
            if (values != null && values.Length == 0)
                values = new object[] { (int)optor };

            if (values == null || optor == SearchOperator.In)
            {
                SearchExpression expression = new SearchExpression(logic, field, values, optor);

                string key = Serializer.CreateMD5(expressions);
                expressions.Add(key, expression);
            }
            else
            {
                foreach (var value in values)
                {
                    SearchExpression expression = new SearchExpression(logic, field, value, optor);

                    string key = Serializer.CreateMD5(expressions);
                    expressions.Add(key, expression);
                }
            }
        }

        /// <summary>
        /// 此SearArgs中查询条件对应类的属性名
        /// </summary>
        /// <typeparam name="T">结果类型</typeparam>
        /// <returns></returns>
        public string ToString<T>()
        {
            StringBuilder builder = new StringBuilder();
            string sql = GetSqlString<T>();

            if (!String.IsNullOrWhiteSpace(sql))
                builder.Append(" WHERE ");

            builder.Append(sql);

            return builder.ToString();
        }

        private string GetSqlString<T>()
        {
            StringBuilder builder = new StringBuilder();
            string aliasStr = string.Empty;

            Dictionary<string, ColumnInfo> dicColumns = DbMapper.GetColumnsDic<T>();
            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T>();

            if (dicBelongs != null && dicBelongs.Count > 0)
            {
                aliasStr = DbMapper.PRIMARY_TABLE_ALIAS + ".";
            }

            //获取 OR 查询部分
            builder.Append(GetOrSql(aliasStr, dicColumns, dicBelongs));

            if (HasOr && HasAnd)
                builder.Append(" AND ");

            //获取 AND 查询部分
            builder.Append(GetAndSql(aliasStr, dicColumns, dicBelongs));

            //获取 扩展 查询部分
            builder.Append(builderExt.ToString());

            return builder.ToString();
        }

        /// <summary>
        /// 此SearchArgs中查询条件对应数据库中的列名
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            StringBuilder builder = new StringBuilder();

            if (!String.IsNullOrEmpty(this.DateField))
            {
                if (this.DateStart != default(DateTime))
                {
                    SearchExpression se = new SearchExpression(SearchLogic.And, this.DateField, this.DateStart, SearchOperator.Ge);
                    String key = Serializer.CreateMD5(se);
                    if (!expressions.Keys.Contains(key))
                        expressions.Add(key, se);
                    //this.Add(SearchLogic.And, this.DateField, SearchOperator.Ge, this.DateStart);
                }
                if (this.DateEnd != default(DateTime))
                {
                    SearchExpression se = new SearchExpression(SearchLogic.And, this.DateField, this.DateEnd, SearchOperator.Le);
                    String key = Serializer.CreateMD5(se);
                    if (!expressions.Keys.Contains(key))
                        expressions.Add(key, se);
                    //this.Add(SearchLogic.And, this.DateField, SearchOperator.Le, this.DateEnd);
                }
            }

            string sql = GetSqlString();

            if (!String.IsNullOrWhiteSpace(sql))
                builder.Append(" WHERE ");

            builder.Append(sql);

            return builder.ToString();
        }

        private string GetSqlString()
        {
            StringBuilder builder = new StringBuilder();

            //获取 OR 查询部分
            builder.Append(GetOrSql());

            if (HasOr && HasAnd)
                builder.Append(" AND ");

            //获取 AND 查询部分
            builder.Append(GetAndSql());

            //获取 扩展 查询部分
            builder.Append(builderExt.ToString());

            return builder.ToString();
        }

        /// <summary>
        /// 获取对象查询参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<IDataParameter> GetDataParameters<T>()
        {
            List<IDataParameter> list = new List<IDataParameter>();
            Dictionary<string, BelongsInfo> dicBelongs = DbMapper.GetBelongsColumnsDic<T>();

            int idx_f = 0;

            foreach (var pName in fields.Keys)
            {
                idx_f++;

                var name = FormatParameterName(pName, idx_f);
                var value = fields[pName];

                if (value == null)//空值
                    list.Add(new DataParameter("@" + name, DBNull.Value));
                else if (dicBelongs.Keys.Contains(pName) && dicBelongs[pName].Type == value.GetType()) //查询值是一个实体，取出实体对应的主键值
                {
                    ColumnInfo primaryKey = DbMapper.GetPrimaryKey(value.GetType());
                    value = ReflectionHelper.GetPropertyValue(value, primaryKey.PropertyName);
                    list.Add(new DataParameter("@" + name, value));
                }
                else if (fields[pName] is Enum) //枚举值
                    list.Add(new DataParameter("@" + name, (int)value));
                else if (fields[pName] is Boolean) //布尔值
                    list.Add(new DataParameter("@" + name, (bool)value ? 1 : 0));
                else if (StringHelper.IsDateTime(fields[pName]) || fields[pName] is IList) //时间或集合
                    continue;
                else
                    list.Add(new DataParameter("@" + name, "%" + value + "%"));
            }

            int idx_e = 0;

            foreach (var key in expressions.Keys)
            {
                idx_e++;

                SearchExpression se = expressions[key];
                var value = se.PropertyValue;

                if (value == null) //空值
                    value = DBNull.Value;
                else if (dicBelongs.Keys.Contains(se.PropertyName) && dicBelongs[se.PropertyName].Type == value.GetType())
                {
                    ColumnInfo primaryKey = DbMapper.GetPrimaryKey(value.GetType());
                    value = ReflectionHelper.GetPropertyValue(value, primaryKey.PropertyName);
                }
                else if (se.PropertyValue is Enum)
                    value = (int)se.PropertyValue;
                else if (se.PropertyValue is Boolean)
                    value = (bool)se.PropertyValue ? 1 : 0;
                else if (se.PropertyValue is IList)
                    continue;
                else if (se.Operator == SearchOperator.InSql)
                    continue;

                list.Add(new DataParameter("@" + FormatParameterName(se.PropertyName, idx_e), value));
            }

            if (this.parametersExt.Count > 0)
                list.AddRange(this.parametersExt);

            return list;
        }

        /// <summary>
        /// 获取数据表查询参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<IDataParameter> GetDataParameters()
        {
            List<IDataParameter> list = new List<IDataParameter>();

            int idx_f = 0;
            foreach (var pName in fields.Keys)
            {
                idx_f++;

                if (fields[pName] == null)
                    list.Add(new DataParameter("@" + FormatParameterName(pName, idx_f), DBNull.Value));
                else if (String.IsNullOrEmpty(fields[pName].ToString()))
                    list.Add(new DataParameter("@" + FormatParameterName(pName, idx_f), string.Empty));
                else if (StringHelper.IsDateTime(fields[pName]) || fields[pName] is IList) //日期或集合 不参数化
                    continue;
                else
                    list.Add(new DataParameter("@" + FormatParameterName(pName, idx_f), "%" + fields[pName] + "%"));
            }

            int idx_e = 0;
            foreach (var key in expressions.Keys)
            {
                idx_e++;

                SearchExpression se = expressions[key];
                if (se.PropertyValue == null)
                    continue;
                else if (se.PropertyValue is IList)
                    continue;
                else if (se.PropertyValue is Enum)
                    se.PropertyValue = (int)se.PropertyValue;
                else if (se.PropertyValue is Boolean)
                    se.PropertyValue = ((bool)se.PropertyValue) ? 1 : 0;
                else if (se.Operator == SearchOperator.InSql)
                    continue;

                list.Add(new DataParameter("@" + FormatParameterName(se.PropertyName, idx_e), se.PropertyValue));
            }

            if (this.parametersExt.Count > 0)
                list.AddRange(this.parametersExt);

            return list;
        }

        /// <summary>
        /// 清空查询项
        /// </summary>
        public void Clear()
        {
            this.fields.Clear();
            this.expressions.Clear();
        }

        private string GetOrSql(string aliasStr, Dictionary<string, ColumnInfo> dicColumns, Dictionary<string, BelongsInfo> dicBelongs)
        {
            StringBuilder builderOr = new StringBuilder();

            // OR 
            if (HasOr)
                builderOr.Append("(");

            int idx_f = 0;
            foreach (var propertyName in fields.Keys)
            {
                idx_f++;

                string parameterName = FormatParameterName(propertyName, idx_f);
                object value = fields[propertyName];

                string columnAlias = string.Empty;

                if (dicColumns.ContainsKey(propertyName))
                {
                    columnAlias = aliasStr + dicColumns[propertyName].Name;

                    if (StringHelper.IsDateTime(value))
                    {
                        DateTime input = StringHelper.ToDateTime(value);
                        string start = DateHelper.GetStartOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");
                        string end = DateHelper.GetEndOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");

                        builderOr.Append(" " + columnAlias + " BETWEEN '" + start + "' AND '" + end + "'");
                    }
                    else if (value is Enum)
                        builderOr.Append(" " + columnAlias + " = @" + propertyName);
                    else if (value == null)
                        builderOr.Append(" " + columnAlias + " IS NULL");
                    else
                        builderOr.Append(" " + columnAlias + " LIKE @" + parameterName);
                }
                else if (dicBelongs.ContainsKey(propertyName))
                {
                    BelongsInfo bc = dicBelongs[propertyName]; //找到对应的外键信息
                    columnAlias = DbMapper.PRIMARY_TABLE_ALIAS + "." + bc.Name;

                    if (value == null)
                        builderOr.Append(" " + columnAlias + " IS NULL");
                    else
                        builderOr.Append(" " + columnAlias + " LIKE @" + parameterName);
                }
                else if (propertyName.Contains("."))
                {
                    string[] temp = propertyName.Split('.'); // Aaa.Bbb.Ccc => Aaa.Bbb、Ccc
                    string lastPName = temp[temp.Length - 1];//Ccc
                    string firstPName = propertyName.Substring(0, propertyName.LastIndexOf(".")); //Aaa.Bbb

                    BelongsInfo bc = dicBelongs[firstPName];  //找到对应的外键信息（表别名）

                    //找到外键表的列
                    int cascade = propertyName.Split('.').Length;
                    List<ColumnInfo> bcColumns = DbMapper.GetColumns(bc.Type, cascade);
                    ColumnInfo bcColumn = bcColumns.Find(c => c.PropertyName == lastPName);
                    if (bcColumn != null)
                    {
                        columnAlias = bc.Alias + "." + bcColumn.Name;

                        if (value == null)
                            builderOr.Append(" " + columnAlias + " IS NULL");
                        else if (StringHelper.IsDateTime(value))
                        {
                            DateTime input = StringHelper.ToDateTime(value);
                            string start = DateHelper.GetStartOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");
                            string end = DateHelper.GetEndOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");

                            builderOr.Append(" " + columnAlias + " BETWEEN '" + start + "' AND '" + end + "'");
                        }
                        else
                            builderOr.Append(" " + columnAlias + " LIKE @" + parameterName);
                    }
                    else
                        throw new Exception("不支持按 " + propertyName + " 查询");
                }
                else
                {
                    throw new Exception(" 不支持按 " + propertyName + " 查询，请检查属性是否存在");
                }
                builderOr.Append(" OR ");
            }

            if (HasOr)
            {
                builderOr.Remove(builderOr.Length - 3, 3);
                builderOr.Append(")");
            }

            return builderOr.ToString();
        }

        private string GetAndSql(string aliasStr, Dictionary<string, ColumnInfo> dicColumns, Dictionary<string, BelongsInfo> dicBelongs)
        {
            StringBuilder builderAnd = new StringBuilder();
            // AND
            int idx_e = 0;

            StringBuilder builder = new StringBuilder();

            foreach (string key in expressions.Keys)
            {
                idx_e++;
                builder.Clear();
                string columnName = string.Empty;

                SearchExpression se = expressions[key];
                if (dicColumns.ContainsKey(se.PropertyName))
                {
                    columnName = (aliasStr + dicColumns[se.PropertyName].Name);
                }
                else if (dicBelongs.ContainsKey(se.PropertyName))
                {
                    BelongsInfo bc = dicBelongs[se.PropertyName]; //找到对应的外键信息
                    columnName = (DbMapper.PRIMARY_TABLE_ALIAS + "." + bc.Name);
                }
                else if (se.PropertyName.Contains("."))
                {
                    string[] temp = se.PropertyName.Split('.'); // Aaa.Bbb.Ccc => Aaa.Bbb、Ccc
                    string lastPName = temp[temp.Length - 1]; //Ccc
                    string firstPName = se.PropertyName.Substring(0, se.PropertyName.LastIndexOf(".")); //Aaa.Bbb

                    BelongsInfo bc = dicBelongs[firstPName];  //找到对应的外键信息（表别名）

                    //找到外键表的列
                    int cascade = se.PropertyName.Split('.').Length;
                    List<ColumnInfo> bcColumns = DbMapper.GetColumns(bc.Type, cascade);
                    ColumnInfo bcColumn = bcColumns.Find(c => c.PropertyName == lastPName);
                    if (bcColumn != null)
                        columnName = (bc.Alias + "." + bcColumn.Name);
                    else
                        throw new Exception("不支持按 " + se.PropertyName + " 查询");
                }
                else
                {
                    continue;
                }

                builder.Append(columnName);

                string nullValue = null;

                if (se.Operator == SearchOperator.Ge)
                    builder.Append(">=");
                else if (se.Operator == SearchOperator.Gt)
                    builder.Append(">");
                else if (se.Operator == SearchOperator.Le)
                    builder.Append("<=");
                else if (se.Operator == SearchOperator.Lt)
                    builder.Append("<");
                else if (se.Operator == SearchOperator.Not && se.PropertyValue != null)
                    builder.Append("<>");
                else if (se.Operator == SearchOperator.Like)
                    builder.Append(" LIKE ");
                else if (se.PropertyValue == null)
                {
                    if (se.Operator == SearchOperator.Not)
                        builder.Append(" IS NOT NULL ");
                    else
                        builder.Append(" IS NULL ");
                }
                else if (se.Operator == SearchOperator.In && se.PropertyValue is IList)
                {
                    builder.Append(" IN (");

                    IList values = se.PropertyValue as IList;
                    foreach (var item in values)
                    {
                        if (item is Enum || item is int)
                            builder.Append((int)item + ",");
                        else if (item is decimal)
                            builder.Append((decimal)item + ",");
                        else if (item is double)
                            builder.Append((double)item + ",");
                        else if (item is float)
                            builder.Append((float)item + ",");
                        else if (item is bool)
                            builder.Append((((bool)item) ? 1 : 0) + ",");
                        else if (item == null)
                            nullValue = " NULL ";
                        else
                            builder.Append("'" + item + "',");
                    }
                    builder.Remove(builder.Length - 1, 1);
                    builder.Append(" ) ");
                }
                else if (se.Operator == SearchOperator.InSql)
                {
                    builder.Append(" IN (");
                    builder.Append(se.PropertyValue);
                    builder.Append(" ) ");
                }
                else
                    builder.Append("=");

                if (se.PropertyValue != null && !(se.PropertyValue is IList) && se.Operator != SearchOperator.InSql)
                    builder.Append("@" + FormatParameterName(se.PropertyName, idx_e));


                if (nullValue != null)
                {
                    builder.Insert(0, "(");
                    builder.Append(" OR " + columnName + " IS NULL)");
                }

                if (se.Logic == SearchLogic.Or)
                    builder.Append(" OR  ");
                else
                    builder.Append(" AND ");

                builderAnd.Append(builder.ToString());
            }
            if (HasAnd)
            {
                builderAnd.Remove(builderAnd.Length - 4, 4);
            }
            return builderAnd.ToString();
        }

        private string GetOrSql()
        {
            StringBuilder builderOr = new StringBuilder();

            // OR 
            if (HasOr)
                builderOr.Append(" (");
            int idx_f = 0;
            foreach (var column in fields.Keys)
            {
                idx_f++;
                string parameterName = FormatParameterName(column, idx_f);
                object value = fields[column];
                if (StringHelper.IsDateTime(value))
                {
                    DateTime input = StringHelper.ToDateTime(value);
                    string start = DateHelper.GetStartOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");
                    string end = DateHelper.GetEndOfDay(input).ToString("yyyy-MM-dd HH:mm:ss");

                    builderOr.Append(column + " BETWEEN '" + start + "' AND '" + end + "'");
                }
                else if (value == null)
                    builderOr.Append(" " + column + " IS NULL");
                else
                    builderOr.Append(column + " LIKE @" + parameterName);

                builderOr.Append(" OR ");
            }

            if (HasOr)
            {
                builderOr.Remove(builderOr.Length - 3, 3);
                builderOr.Append(")");
            }

            return builderOr.ToString();
        }

        private string GetAndSql()
        {
            StringBuilder builderAnd = new StringBuilder();
            // AND
            int idx_e = 0;

            StringBuilder builder = new StringBuilder();

            foreach (String key in expressions.Keys)
            {
                idx_e++;
                builder.Clear();
                string nullValue = null;

                SearchExpression se = expressions[key];
                builder.Append(" " + se.PropertyName);
                if (se.Operator == SearchOperator.Ge)
                    builder.Append(">=");
                else if (se.Operator == SearchOperator.Gt)
                    builder.Append(">");
                else if (se.Operator == SearchOperator.Le)
                    builder.Append("<=");
                else if (se.Operator == SearchOperator.Lt)
                    builder.Append("<");
                else if (se.Operator == SearchOperator.Not && se.PropertyValue != null)
                    builder.Append("<>");
                else if (se.Operator == SearchOperator.Like)
                    builder.Append(" LIKE ");
                else if (se.PropertyValue == null)
                {
                    if (se.Operator == SearchOperator.Not)
                        builder.Append(" IS NOT NULL ");
                    else
                        builder.Append(" IS NULL ");
                }
                else if (se.Operator == SearchOperator.In && se.PropertyValue is IList)
                {
                    builder.Append(" IN (");

                    IList values = se.PropertyValue as IList;
                    foreach (var item in values)
                    {
                        if (item is Enum || item is int)
                            builder.Append((int)item + ",");
                        else if (item is decimal)
                            builder.Append((decimal)item + ",");
                        else if (item is double)
                            builder.Append((double)item + ",");
                        else if (item is float)
                            builder.Append((float)item + ",");
                        else if (item is bool)
                            builder.Append((((bool)item) ? 1 : 0) + ",");
                        else if (item == null)
                            nullValue = " NULL ";
                        else
                            builder.Append("'" + item + "',");
                    }
                    builder.Remove(builder.Length - 1, 1);
                    builder.Append(" ) ");
                }
                else if (se.Operator == SearchOperator.InSql)
                {
                    builder.Append(" IN (");
                    builder.Append(se.PropertyValue);
                    builder.Append(" ) ");
                }
                else
                    builder.Append("=");

                if (se.PropertyValue != null && !(se.PropertyValue is IList) && se.Operator != SearchOperator.InSql)
                    builder.Append("@" + FormatParameterName(se.PropertyName, idx_e));

                if (nullValue != null)
                {
                    builder.Insert(0, "(");
                    builder.Append(" OR " + se.PropertyName + " IS NULL)");
                }

                if (se.Logic == SearchLogic.Or)
                    builder.Append(" OR ");
                else
                    builder.Append(" AND");

                builderAnd.Append(builder.ToString());
            }
            if (HasAnd)
            {
                builderAnd.Remove(builderAnd.Length - 3, 3);
            }

            return builderAnd.ToString();
        }


        /// <summary>
        /// 格式化参数名称
        /// </summary>
        /// <param name="inputName"></param>
        /// <returns></returns>
        private string FormatParameterName(string inputName, int idx)
        {
            return inputName.Replace(".", "_") + idx.ToString();
        }


    }
}
